﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO.Packaging;
using System.Xml;

using Ins.Packaging;

namespace Ins.Office
{
    /// <summary>
    /// Provides standard functionalities for manipulating Excel worksheets.
    /// </summary>
    public class XLUtil
    {

        /// <summary>
        /// Returns a dictionary object containing the shared strings used by an Excel workbook.
        /// </summary>
        /// <param name="workbookPath">The full path of the Excel workbook.</param>
        /// <returns>A IDictionary&lt;string, int&gt; object.</returns>
        /// <remarks>
        /// The shared strings dictionary uses keys that are the actual strings, whereas the values are indexes in the shared strings part.
        /// The shared strings features is used by Excel to minimize the file size, by using pointers in the worksheet data, rather then actual strings.
        /// </remarks>
        public static IDictionary<string, int> GetSharedStringsDictionary(string workbookPath)
        {
            IDictionary<string, int> dict = new Dictionary<string, int>();
            using(Package pkg = Package.Open(workbookPath, System.IO.FileMode.Open))
            {
                XLPackageSettings settings = new XLPackageSettings();
                Pack p = new Pack(pkg, settings);
                PackagePart docPart = p.GetDocumentPart();
                string partPath = "sharedStrings.xml";
                PackagePart part = p.GetPart(docPart, XLPackageSettings.SharedStringsRelationType, partPath);
                int index = 0;
                using(XmlReader rdr = XmlReader.Create(part.GetStream()))
                {
                    while (rdr.Read())
                    {
                        switch (rdr.NodeType)
                        {
                            case XmlNodeType.Element:
                                switch (rdr.Name)
                                {
                                    case "sst":
                                        break;
                                    case "t":
                                    string value = rdr.ReadElementContentAsString();
                                    if (!dict.ContainsKey(value))
                                    {
                                        dict.Add(value, index);
                                        index++;
                                    }
                                        break;
                                    default:
                                        break;
                                }
                                break;
                        }
                    }
                }
            }
            return dict;
        }

        /// <summary>
        /// Writes the heading of an Excel spreadsheet.
        /// </summary>
        /// <param name="writer">The XmlWriter object to write data to.</param>
        internal static void WriteSheetStart(XmlWriter writer)
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("worksheet", XLPackageSettings.DefaultNS);
            writer.WriteAttributeString("xmlns", "r", XLPackageSettings.XmlnsNS, XLPackageSettings.RelationshipNS);
            writer.WriteStartElement("sheetViews");
            writer.WriteStartElement("sheetView");
            writer.WriteAttributeString("tabSelected", "1");
            writer.WriteAttributeString("workbookViewId", "0");
            writer.WriteStartElement("selection");
            writer.WriteAttributeString("activeCell", "A1");
            writer.WriteAttributeString("sqref", "A1");
            writer.WriteEndElement(); //selection
            writer.WriteEndElement(); //sheetView
            writer.WriteEndElement(); //sheetViews
            writer.WriteStartElement("sheetFormatPr");
            writer.WriteAttributeString("defaultRowHeight", "12.75");
            writer.WriteEndElement(); //sheetFormatPr
        }

        /// <summary>
        /// Writes the end part of an Excel spreadsheet.
        /// </summary>
        /// <param name="writer">The XmlWriter object to write data to.</param>
        internal static void WriteSheetEnd(XmlWriter writer)
        {
            writer.WriteStartElement("phoneticPr");
            writer.WriteAttributeString("fontId", "1");
            writer.WriteAttributeString("type", "noConversion");
            writer.WriteEndElement(); //phoneticPr

            writer.WriteStartElement("pageMargins");
            writer.WriteAttributeString("left", "0.75");
            writer.WriteAttributeString("right", "0.75");
            writer.WriteAttributeString("top", "1");
            writer.WriteAttributeString("bottom", "1");
            writer.WriteAttributeString("header", "0.5");
            writer.WriteAttributeString("footer", "0.5");
            writer.WriteEndElement(); //pageMargins

            writer.WriteStartElement("pageSetup");
            writer.WriteAttributeString("orientation", "portrait");
            //writer.WriteAttributeString("r:Id", "rId1");
            writer.WriteEndElement(); //pageSetup

            writer.WriteStartElement("headerFooter");
            writer.WriteAttributeString("alignWithMargins", "0");
            writer.WriteEndElement(); //headerFooter

            writer.WriteEndElement(); //worksheet
            writer.WriteEndDocument();
        }
        /// <summary>
        /// Writes a value element to the spreadsheet.
        /// </summary>
        /// <param name="writer">The XmlWriter object associated with the spreadsheet.</param>
        /// <param name="typeName">The .NET type name of the </param>
        /// <param name="value">The value to be written.</param>
        /// <param name="index">The index value used when writing string values in the shared strings dictionary.</param>
        internal static void WriteValue(XmlWriter writer, string typeName, string value, IDictionary<string, int> sharedStringsDict, ref int index)
        {
            switch (typeName)
            {
                case "System.Boolean":
                    bool bValue = false;
                    bool.TryParse(value, out bValue);
                    writer.WriteAttributeString("t", "b");
                    writer.WriteStartElement("v");
                    writer.WriteValue(bValue);
                    writer.WriteEndElement(); //v
                    break;
                case "System.DateTime":
                    DateTime dValue;
                    DateTime.TryParse(value, out dValue);
                    writer.WriteAttributeString("s", "1");
                    writer.WriteStartElement("v");
                    writer.WriteValue(dValue.ToOADate());
                    writer.WriteEndElement(); //v
                    break;
                case "System.Byte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Decimal":
                case "System.Single":
                case "System.Double":
                    writer.WriteStartElement("v");
                    writer.WriteValue(value);
                    writer.WriteEndElement(); //v
                    break;
                default:
                    writer.WriteAttributeString("t", "s");
                    writer.WriteStartElement("v");
                    if (sharedStringsDict.ContainsKey(value))
                    {
                        writer.WriteString(sharedStringsDict[value].ToString());
                    }
                    else
                    {
                        sharedStringsDict.Add(value, index);
                        writer.WriteString(index.ToString());
                        index++;
                    }
                    writer.WriteEndElement(); //v
                    break;
            } //switch type
        }

        /// <summary>
        /// Writes a shared strings dictionary to a Xml file.
        /// </summary>
        /// <param name="sharedStringsDict">The dictionary object containg the shared strings.</param>
        /// <param name="fileName">The output Xml file name.</param>
        public static void WriteSharedStrings(IDictionary<string, int> sharedStringsDict, string fileName)
        {
            using (XmlWriter w = XmlTextWriter.Create(fileName))
            {
                w.WriteStartDocument();
                w.WriteStartElement("sst", XLPackageSettings.DefaultNS);
                w.WriteAttributeString("count", sharedStringsDict.Count.ToString());
                w.WriteAttributeString("uniqueCount", sharedStringsDict.Count.ToString());
                foreach (string k in sharedStringsDict.Keys)
                {
                    w.WriteStartElement("si");
                    w.WriteStartElement("t");
                    w.WriteString(k);
                    w.WriteEndElement();
                    w.WriteEndElement();
                }
                w.WriteEndElement();
                w.WriteEndDocument();
            }
        }

    }
}
